package com.b2c.repository;

import com.b2c.entity.vo.GoodsCategoryAnalyseVo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;
@Repository
public class GoodsCategoryAnalyseRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    public List<GoodsCategoryAnalyseVo> getAnalyseReport() {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT ");
        sql.append("gc.`name`AS categoryName,gc.id AS categoryId,(SELECT `name` FROM erp_goods_category WHERE id=gc.parent_id) AS parentName");
        sql.append(",(SELECT COUNT(0) FROM erp_goods AS g WHERE g.categoryId=gc.id) AS goodsCount");
        sql.append(",(SELECT COUNT(0) FROM erp_goods_spec AS gs WHERE gs.goodsId IN (SELECT id FROM erp_goods WHERE categoryId = gc.id)) AS skuCount");
        sql.append(",IFNULL((SELECT SUM(gsi.currentQty) FROM erp_goods_stock_info AS gsi WHERE gsi.goodsId IN (SELECT id FROM erp_goods WHERE categoryId = gc.id)),0) AS currentQty");
        sql.append(",IFNULL((SELECT SUM(sifi.quantity) FROM erp_stock_in_form_item AS sifi WHERE sifi.goodsId IN (SELECT id FROM erp_goods WHERE categoryId = gc.id)),0) AS buyQty");
        sql.append(",IFNULL((SELECT SUM(oi.quantity) FROM erp_order_item AS oi WHERE oi.productId IN (SELECT id FROM erp_goods WHERE categoryId = gc.id) ),0) AS salesCount");
        sql.append(" FROM erp_goods_category AS gc ");
        sql.append(" WHERE gc.isDelete = 0 AND gc.parent_id <> 0 ");
        sql.append(" ORDER BY gc.parent_id ASC ");
        return jdbcTemplate.query(sql.toString(),new BeanPropertyRowMapper<>(GoodsCategoryAnalyseVo.class));
    }
}

